using Dapper;
using Lzfy_Refund_Service.Models;
using Lzfy_Refund_Service.Services;
using System.Data;

namespace Lzfy_Refund_Service.Repositories
{
    public class CallLogRepository : ICallLogRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<CallLogRepository> _logger;

        public CallLogRepository(IDbConnectionFactory connectionFactory, ILogger<CallLogRepository> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        public async Task<CallLog?> GetByIdAsync(int id)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var sql = @"SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    username AS Username,
                    fullname AS Fullname,
                    call_phone AS CallPhone,
                    user_phone AS UserPhone,
                    send_msg AS SendMsg
                    FROM lzfy_call_log WHERE id = @Id";
                return await connection.QueryFirstOrDefaultAsync<CallLog>(sql, new { Id = id });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取通话记录失败，ID: {Id}", id);
                throw;
            }
        }

        public async Task<IEnumerable<CallLog>> GetAllAsync()
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var sql = @"SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    username AS Username,
                    fullname AS Fullname,
                    call_phone AS CallPhone,
                    user_phone AS UserPhone,
                    send_msg AS SendMsg
                    FROM lzfy_call_log ORDER BY input_date DESC";
                return await connection.QueryAsync<CallLog>(sql);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取所有通话记录失败");
                throw;
            }
        }

        public async Task<IEnumerable<CallLog>> GetByUsernameAsync(string username)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var sql = @"SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    username AS Username,
                    fullname AS Fullname,
                    call_phone AS CallPhone,
                    user_phone AS UserPhone,
                    send_msg AS SendMsg
                    FROM lzfy_call_log WHERE username = @Username ORDER BY input_date DESC";
                return await connection.QueryAsync<CallLog>(sql, new { Username = username });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据用户名获取通话记录失败，用户名: {Username}", username);
                throw;
            }
        }

        public async Task<IEnumerable<CallLog>> GetByCallPhoneAsync(string callPhone)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var sql = @"SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    username AS Username,
                    fullname AS Fullname,
                    call_phone AS CallPhone,
                    user_phone AS UserPhone,
                    send_msg AS SendMsg
                    FROM lzfy_call_log WHERE call_phone = @CallPhone ORDER BY input_date DESC";
                return await connection.QueryAsync<CallLog>(sql, new { CallPhone = callPhone });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据呼叫电话获取通话记录失败，电话: {CallPhone}", callPhone);
                throw;
            }
        }

        public async Task<IEnumerable<CallLog>> GetRecentAsync(int minutes = 60)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var sql = @"SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    username AS Username,
                    fullname AS Fullname,
                    call_phone AS CallPhone,
                    user_phone AS UserPhone,
                    send_msg AS SendMsg
                    FROM lzfy_call_log 
                    WHERE input_date >= DATEADD(MINUTE, -@Minutes, GETDATE())
                    ORDER BY input_date DESC";
                return await connection.QueryAsync<CallLog>(sql, new { Minutes = minutes });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取最近通话记录失败，时间范围: {Minutes}分钟", minutes);
                throw;
            }
        }

        public async Task<(IEnumerable<CallLog> data, int totalCount)> GetPagedAsync(
            int pageIndex = 1,
            int pageSize = 20,
            string? username = null,
            string? callPhone = null,
            DateTime? startDate = null,
            DateTime? endDate = null)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                
                var whereConditions = new List<string>();
                var parameters = new DynamicParameters();
                
                if (!string.IsNullOrEmpty(username))
                {
                    whereConditions.Add("username LIKE @Username");
                    parameters.Add("Username", $"%{username}%");
                }
                
                if (!string.IsNullOrEmpty(callPhone))
                {
                    whereConditions.Add("call_phone LIKE @CallPhone");
                    parameters.Add("CallPhone", $"%{callPhone}%");
                }
                
                if (startDate.HasValue)
                {
                    whereConditions.Add("input_date >= @StartDate");
                    parameters.Add("StartDate", startDate.Value);
                }
                
                if (endDate.HasValue)
                {
                    var endDateTime = endDate.Value.Date.AddDays(1).AddTicks(-1);
                    whereConditions.Add("input_date <= @EndDate");
                    parameters.Add("EndDate", endDateTime);
                }
                
                var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
                
                // 查询总数
                var countSql = $"SELECT COUNT(*) FROM lzfy_call_log {whereClause}";
                var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);
                
                // 查询分页数据
                var offset = (pageIndex - 1) * pageSize;
                parameters.Add("Offset", offset);
                parameters.Add("PageSize", pageSize);
                
                var dataSql = $@"SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    username AS Username,
                    fullname AS Fullname,
                    call_phone AS CallPhone,
                    user_phone AS UserPhone,
                    send_msg AS SendMsg
                    FROM lzfy_call_log {whereClause}
                    ORDER BY input_date DESC
                    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
                
                var data = await connection.QueryAsync<CallLog>(dataSql, parameters);
                
                return (data, totalCount);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "分页查询通话记录失败");
                throw;
            }
        }

        public async Task<int> CreateAsync(CallLog callLog)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var sql = @"INSERT INTO lzfy_call_log (username, fullname, call_phone, user_phone, send_msg) 
                           VALUES (@Username, @Fullname, @CallPhone, @UserPhone, @SendMsg);
                           SELECT CAST(SCOPE_IDENTITY() as int)";
                return await connection.QuerySingleAsync<int>(sql, callLog);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "创建通话记录失败");
                throw;
            }
        }

        public async Task<bool> UpdateAsync(CallLog callLog)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var sql = @"UPDATE lzfy_call_log 
                           SET username = @Username, 
                               fullname = @Fullname,
                               call_phone = @CallPhone,
                               user_phone = @UserPhone,
                               send_msg = @SendMsg
                           WHERE id = @Id";
                var rowsAffected = await connection.ExecuteAsync(sql, callLog);
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "更新通话记录失败，ID: {Id}", callLog.Id);
                throw;
            }
        }

        public async Task<bool> DeleteAsync(int id)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var sql = "DELETE FROM lzfy_call_log WHERE id = @Id";
                var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "删除通话记录失败，ID: {Id}", id);
                throw;
            }
        }
    }
}